﻿using Model;
using System;
using System.Data;
using System.Text;

namespace BLL
{
    public class Lc_NodeBLL : BaseBLL
    {
        public Lc_NodeBLL()
        {
        }

        public Lc_NodeBLL(Service service)
        {
            this.Service = service;
        }

        public DataTable GetAllInfo(string Style, object param)
        {
            string sql = string.Format(@"SELECT
    lcn.N_NODE_ID,
    lcn.C_NODE_NAME,
    lcn.N_DOIING_TIME,
    CASE lcn.N_NODE_STATE
    WHEN 0 THEN '在使用'
    ELSE '未使用'
    END N_NODE_STATE,
    lcn.N_TRANSACTION_TIME,
    CASE lcn.N_NODE_TYPE
    WHEN 0 THEN '审核节点'
    ELSE '办理节点'
    END N_NODE_TYPE,
    lcn.N_NODE_OPERATOR_ID,
    lcn.N_NODE_OPERATION_TIME,
    lcn.N_PROCESS_ID,
    sysu.USER_NAME,
    lcp.C_PROCESS_NAME
FROM
    Lc_Node lcn,
    Sys_User sysu,
    Lc_Process lcp
WHERE
    lcn.N_NODE_OPERATOR_ID=sysu.USER_ID
    AND lcn.N_PROCESS_ID=lcp.N_PROCESS_ID
    AND lcn.N_NODE_STATE=0 and lcp.n_process_state=1 ");
            if (Style == "Contion")
            {
                string[] strobj = (string[])param;
                if (!string.IsNullOrEmpty(strobj[0].ToString()))
                {
                    sql += string.Format(@" AND lcn.C_NODE_NAME like '%" + strobj[0].ToString() + "%'");
                }
                if (!string.IsNullOrEmpty(strobj[1]))
                {
                    sql += string.Format(@" AND lcn.N_PROCESS_ID='" + decimal.Parse(strobj[1]) + "'");
                }
                if (!strobj[3].Equals("-1"))
                {
                    sql += string.Format(@" AND lcn.N_NODE_STATE=" + strobj[3]);
                }
                if (!strobj[4].Equals("-1"))
                {
                    sql += string.Format(@" AND lcn.N_NODE_TYPE=" + strobj[4]);
                }
            }
            else if (Style == "NodeName")
            {
                sql += " AND lcn.C_NODE_NAME='" + param + "'";
            }
            else if (Style == "ProcessID")
            {
                sql += " AND lcn.N_PROCESS_ID=" + param;
            }

            sql += " ORDER BY N_NODE_ID  DESC";
            // DataTable dt = DbHelperSQL.GetDataTable(sql);

            DataTable dt = Query(sql, "", "GetAllInfo", "查询有效节点", "");
            return dt;
        }

        public DataTable GetAllInfoSel(string name, string pid)
        {
            string sql = string.Format(@"SELECT
    lcn.N_NODE_ID,
    lcn.C_NODE_NAME,
    lcn.N_DOIING_TIME,
    CASE lcn.N_NODE_STATE
    WHEN 0 THEN '在使用'
    ELSE '未使用'
    END N_NODE_STATE,
    lcn.N_TRANSACTION_TIME,
    CASE lcn.N_NODE_TYPE
    WHEN 0 THEN '审核节点'
    ELSE '办理节点'
    END N_NODE_TYPE,
    lcn.N_NODE_OPERATOR_ID,
    lcn.N_NODE_OPERATION_TIME,
    lcn.N_PROCESS_ID,
    sysu.USER_NAME,
    lcp.C_PROCESS_NAME
FROM
    Lc_Node lcn,
    Sys_User sysu,
    Lc_Process lcp
WHERE
    lcn.N_NODE_OPERATOR_ID=sysu.USER_ID
    AND lcn.N_PROCESS_ID=lcp.N_PROCESS_ID
    AND lcn.N_NODE_STATE=0 and lcp.n_process_state=1 ");

            sql += " AND lcn.C_NODE_NAME='" + name + "'";

            sql += " AND lcn.N_PROCESS_ID=" + pid;

            sql += " ORDER BY N_NODE_ID  DESC";
            // DataTable dt = DbHelperSQL.GetDataTable(sql);

            DataTable dt = Query(sql, "", "GetAllInfo", "查询有效节点", "");
            return dt;
        }

        public Lc_Node GetInfoById(string id)
        {
            string sql = string.Format(@"SELECT
    lcn.N_NODE_ID,
    lcn.C_NODE_NAME,
    lcn.N_DOIING_TIME,
    lcn.N_NODE_STATE,
    lcn.N_TRANSACTION_TIME,
    lcn.N_NODE_TYPE,
    lcn.N_NODE_OPERATOR_ID,
    lcn.N_NODE_OPERATION_TIME,
    lcn.N_PROCESS_ID
FROM
    Lc_Node lcn
WHERE
    lcn.N_NODE_ID=" + id);
            DataTable dt = Query(sql);
            Lc_Node model = new Lc_Node();

            if (dt != null && dt.Rows.Count > 0)
            {
                model.N_NODE_ID = decimal.Parse(dt.Rows[0]["N_NODE_ID"].ToString());
                model.C_NODE_NAME = dt.Rows[0]["C_NODE_NAME"].ToString();
                model.N_DOIING_TIME = decimal.Parse(dt.Rows[0]["N_DOIING_TIME"].ToString());
                model.N_NODE_STATE = decimal.Parse(dt.Rows[0]["N_NODE_STATE"].ToString());
                model.N_TRANSACTION_TIME = decimal.Parse(dt.Rows[0]["N_TRANSACTION_TIME"].ToString());
                model.N_NODE_TYPE = decimal.Parse(dt.Rows[0]["N_NODE_TYPE"].ToString());
                model.N_NODE_OPERATOR_ID = decimal.Parse(dt.Rows[0]["N_NODE_OPERATOR_ID"].ToString());
                model.N_NODE_OPERATION_TIME = DateTime.Parse(dt.Rows[0]["N_NODE_OPERATION_TIME"].ToString());
                model.N_PROCESS_ID = decimal.Parse(dt.Rows[0]["N_PROCESS_ID"].ToString());
            }
            return model;
        }

        /// <summary>
        /// 查询实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Lc_Node Find(decimal id)
        {
            return GetInfoById(id.ToString());
        }

        public int INSERT(Lc_Node model, string operName)
        {
            model.N_NODE_ID = GetSequence("N_NODE_ID");
            var sql = string.Format(@"INSERT INTO LC_NODE(
	N_NODE_ID,
	C_NODE_NAME,
	N_DOIING_TIME,
	N_NODE_STATE,
	N_TRANSACTION_TIME,
	N_NODE_TYPE,
	N_NODE_OPERATOR_ID,
	N_NODE_OPERATION_TIME,
	N_PROCESS_ID
)
VALUES(
	{0},
	'{1}',
	{2},
	{3},
	{4},
	{5},
	{6},
	TO_DATE('{7}','YYYY-MM-DD HH24:MI:SS'),
	{8}
)", model.N_NODE_ID,
  model.C_NODE_NAME,
  model.N_DOIING_TIME,
  model.N_NODE_STATE,
  model.N_TRANSACTION_TIME,
  model.N_NODE_TYPE,
  model.N_NODE_OPERATOR_ID,
  model.N_NODE_OPERATION_TIME.ToString("yyyy-MM-dd HH:mm:ss"),
  model.N_PROCESS_ID);
            return NoQuery(sql, operName);
        }

        public int UPDATE(Lc_Node model, string operName)
        {
            string sql = string.Format(@"UPDATE THKQS.Lc_Node SET
C_NODE_NAME   = '{0}',
N_PROCESS_ID  ='{1}',
N_NODE_TYPE   = '{2}',
N_NODE_STATE  = '{3}',
N_TRANSACTION_TIME='{4}',
N_DOIING_TIME = {5}
WHERE N_NODE_ID = '{6}'"
, model.C_NODE_NAME, model.N_PROCESS_ID, model.N_NODE_TYPE, model.N_NODE_STATE, model.N_TRANSACTION_TIME, model.N_DOIING_TIME, model.N_NODE_ID);
            int count = NoQuery(sql, operName);
            return count;
        }

        public int DELETE(string ids, string operName)
        {
            string sql = string.Format(@"DELETE FROM THKQS.Lc_Node  WHERE N_NODE_ID IN(" + ids + ")");
            int count = NoQuery(sql, operName);
            return count;
        }

        public int GetRecordCount(string Style, object param)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT count(1)
FROM
    Lc_Node lcn,
    Sys_User sysu,
    Lc_Process lcp
WHERE
    lcn.N_NODE_OPERATOR_ID = sysu.USER_ID
    AND lcn.N_PROCESS_ID = lcp.N_PROCESS_ID ");
            //AND lcn.N_NODE_STATE = 0
            if (Style == "Contion")
            {
                strSql.Append(param);
            }
            else if (Style == "NodeName")
            {
                strSql.Append(param);
            }
            else if (Style == "ProcessID")
            {
                strSql.Append(param);
            }
            return GetCount(strSql.ToString());
        }

        public DataTable GetListByPage(string Style, object param, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY lcn." + orderby + @") as r,
    lcn.N_NODE_ID,
    lcn.C_NODE_NAME,
    lcn.N_DOIING_TIME,
    CASE lcn.N_NODE_STATE
    WHEN 0 THEN '在使用'
    ELSE '未使用'
    END N_NODE_STATE,
    lcn.N_TRANSACTION_TIME,
    CASE lcn.N_NODE_TYPE
    WHEN 0 THEN '审核节点'
    ELSE '办理节点'
    END N_NODE_TYPE,
    lcn.N_NODE_OPERATOR_ID,
    to_char(lcn.N_NODE_OPERATION_TIME,'yyyy-mm-dd HH24:MI:SS') N_NODE_OPERATION_TIME,
    lcn.N_PROCESS_ID,
    sysu.USER_NAME,
    lcp.C_PROCESS_NAME
FROM
    THKQS.Lc_Node lcn,
    THKQS.Sys_User sysu,
    THKQS.Lc_Process lcp
WHERE
    lcn.N_NODE_OPERATOR_ID=sysu.USER_ID
    AND lcn.N_PROCESS_ID=lcp.N_PROCESS_ID
   ");
            if (Style == "Contion")
            {
                strSql.Append(param);
            }
            else if (Style == "NodeName")
            {
                strSql.Append(param);
            }
            else if (Style == "ProcessID")
            {
                strSql.Append(param);
            }

            strSql.Append(@" ) TT");
            if (startIndex >= 2)
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1}+1 AND {0}*{1}", startIndex, endIndex);
            }
            else
            {
                strSql.AppendFormat(" WHERE TT.r BETWEEN ({0}-1)*{1} AND {0}*{1}", startIndex, endIndex);
            }
            return Query(strSql.ToString());
        }
    }
}